In this course, you will learn how to design, deploy, load, manage, and query data warehouses and data marts. You will begin by understanding different analytics repositories including data marts, data warehouses, data lakes, data lakehouses and their functions and uses. They are designed to enable rapid business decision-making through accurate and flexible reporting and data analysis.

A data warehouse is one of the most fundamental business intelligence tools in use today, and one that successful Data Engineers must understand. Additionally, you will learn to design, model, and implement data warehouses and explore data warehousing architectures such as Star and Snowflake schemas. Finally, you will learn how to populate data warehouses using Extract, Transform, and Load (ETL) and Extract, Load, Transform (ELT) processes, verify data, query data, and use Cubes, Rollups, and materialized views/tables.

The hands-on labs in this course will enable you to apply what you learn and gain a practical knowledge of data warehousing. You will work with repositories such as PostgreSQL and IBM Db2.

Learning Objectives:

By the end of this course, you will be able to:

Discuss the fundamentals of data warehouses, data marts, and data lakes by analyzing their features and advantages.
Examine organizations' decision-making processes to select specific data warehouse systems and vendors.
Design and populate a data warehouse, and model and query data using Cubes, Rollups, and materialized views.
Design and load data into a data warehouse, write aggregation queries, create materialized query tables, and create an analytics dashboard.

Course Content

This course is divided into four modules. Each module requires 2-4 hours of learner engagement. It is recommended that you complete one module per week.

Module 1: Data Warehouses, Data Marts, and Data Lakes

Module 2: Designing, Modeling, and Implementing Data Warehouses

Module 3: Final Assignment and Final Quiz

Module 4 (Optional): IBM Db2

Module 1: Data Warehouses, Data Marts, and Data Lakes

This module provides an introduction to data warehouse systems, data lakes, and data marts. When you complete this module, you’ll be able to identify and compare data warehouse systems, data marts, and data lakes based on their architecture, and understand how organizations can benefit from each of these three data storage entities. Then, you’ll learn about three types of data warehouse systems and popular data warehouse system vendors. You will be ready to help your organization assess new data warehouse system offerings when you know the five essential, critical criteria, including the total cost of ownership, to evaluate before changing to a new data warehouse system.

Module 2: Designing, Modeling, and Implementing Data Warehouses

In this knowledge-packed module, you’ll explore general and reference enterprise data warehousing architecture. You’ll discover how data cubes relate to star schemas. Then, you’ll learn how to slice, dice, drill up or down, roll up, and pivot relative to data cubes. Next, you will examine the capabilities of materialized views, their benefits, and how to apply them. You’ll learn how a data organization using facts and dimensions and their related tables organizes information. Then, you will explore how to use normalization to create a snowflake schema as an extension of the star schema. You will also learn about populating a data warehouse, incremental data updates, verifying data, querying data, and interpreting an entity-relationship diagram for a star schema. Finally, the module will delve into the creation of a materialized view, the application of Cube and Rollup options, and examine the advantages organizations gain from implementing staging.

Module 3: Final Assignment and Final Quiz

In this module, you’ll complete your practice project and final course project, which bring together concepts and practices you previously learned in the first two modules. In the final project, you will design and load data into a data warehouse using facts and dimension tables. Then you’ll write aggregation queries using cube and rollup functions and create a materialized view.

Module 4 (Optional): IBM Db2

This module is optional and will enable you explore the workings of IBM Db2 data warehouse system architecture, view use cases, and understand the key capabilities and integrations available with IBM Db2 Warehouse. The hands-on labs in this module will enable you to gain practical knowledge on how to create Db2 service instance, how to populate a data warehouse using IBM Db2, how to query the data warehouse using IBM Db2, and so on.

Prerequisites

To succeed in this course, it is recommended that you should have a fundamental knowledge of Relational Database Management Systems (RDBMS) and working knowledge of Structured Query Language (SQL).

Associated Professional Certificate Programs

This course is a part of two Professional Certificate program series:

This course is suitable for data professionals, including data engineers, data architects, and data warehouse developers who want to excel in their career. It uses simple, easy-to-understand language to explain important concepts without relying on technical jargon. To derive maximum learning from this course, actively participate in and complete the various learning engagements offered across the modules.

Good luck!